#! /bin/bash
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "------------------因为采用了动态分区插入技术 因此需要设置相关参数---------------
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
set hive.support.concurrency=false;

--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;


--INSERT OVERWRITE TABLE oe_dwb.dwb_signup_message PARTITION (dt)
SELECT
    -------------------------指标--------------------
    ---来自：dwd_fact_customer_relationship
    fcr.id AS relationship_id,  --关系id
    CASE WHEN payment_state = 'PAID'
        THEN '已报名'
        ELSE '未报名'
        END AS signup_type, --报名状态
    fcc.id AS clue_id,          --线索id
    fca.clue_status,          --线索状态
    -----------------------------维度------------------------------
    substring(fcr.payment_time,1,10) AS pay_date,       --支付时间天
    substring(fcr.payment_time,1,7) AS pay_month,       --支付时间月
    substring(fcr.payment_time,1,4) AS pay_year,       --支付时间年
    substring(fcr.create_date_time,1,10) AS relationship_create_date,  --关系创建日期
    fcr.customer_id,    --所属客户id
    fcr.origin_channel,    --来源渠道
    fcr.line_type,      --'线上、线下'
    fcr.creator AS creator,        --创建人
    dis.itcast_school_id AS itcast_school_id,   --学校id
    dis.itcast_school_name AS itcast_school_name,  --学校名字
    dis.itcast_subject_id AS itcast_subject_id,     --学科id
    dis.itcast_subject_name AS itcast_subject_name,    --学科名字
    substring(fcc.create_date_time,1,10) AS clue_create_date,   --线索创建日期
    de.id AS employee_id,   --创建人的员工id
    dsd.id  AS department_id,     --创建人的部门id
    dsd.name AS  department_name,     --创建人的部门名字
    '2023-05-28' AS dt              --分区字段
FROM oe_dwd.fact_customer_relationship fcr                                                  --用户关系表
LEFT JOIN oe_dwd.fact_customer_clue fcc ON fcc.customer_relationship_id = fcr.id             --线索表
LEFT JOIN oe_dwd.fact_customer_appeal fca ON fca.customer_relationship_first_id = fcc.id     --申诉表
LEFT JOIN oe_dwd.dim_employee de  ON fcr.creator = de.id                                    --员工表
LEFT JOIN oe_dwd.dim_scrm_department dsd ON de.tdepart_id = dsd.id    --部门表
LEFT JOIN oe_dwd.dim_itcast_clazz dis ON dis.id = fcr.itcast_clazz_id;                       --班级表



"